Excel copy paste sheet

Could someone post DXL code to copy and paste an entire sheet in Excel?

Thanks much!
SystemAdmin - Wed Jul 06 09:50:02 EDT 2011

Re: Excel copy paste sheet
Mathias Mamsch - Thu Jul 07 03:15:22 EDT 2011

I have some old code floating around. Unfortunately it is embedded in a bug Excel library that I cannot post entirely, but something like this should do the trick?

...
    OleAutoArgs args = create()
    put (args, objWorksheetTarget) 
    put (args, "Before") 
    oleMethod (objWorkSheetSource, "Copy", args )
    ...
}

Regards, Mathias

Mathias Mamsch, IT-QBase GmbH, Consultant for Requirement Engineering and D00RS

Re: Excel copy paste sheet
SystemAdmin - Thu Jul 07 09:38:59 EDT 2011

Mathias Mamsch - Thu Jul 07 03:15:22 EDT 2011

I have some old code floating around. Unfortunately it is embedded in a bug Excel library that I cannot post entirely, but something like this should do the trick?

...
    OleAutoArgs args = create()
    put (args, objWorksheetTarget) 
    put (args, "Before") 
    oleMethod (objWorkSheetSource, "Copy", args )
    ...
}

Regards, Mathias

Mathias Mamsch, IT-QBase GmbH, Consultant for Requirement Engineering and D00RS

What types are "objWorksheetTarget" and "objWorkSheetSource"?

Re: Excel copy paste sheet
Mathias Mamsch - Fri Jul 08 06:12:59 EDT 2011

SystemAdmin - Thu Jul 07 09:38:59 EDT 2011
What types are "objWorksheetTarget" and "objWorkSheetSource"?

Sorry I was misreading my old code. Here is a complete Example (start it with an open excel Sheet):

OleAutoObj objEx = oleGetAutoObject("Excel.Application") 
if (null objEx) { print "Please open Excel!"; halt }
 
OleAutoObj objSheet = null 
oleGet(objEx, "ActiveSheet", objSheet)
if (null objEx) { print "Cannot find sheet. Please open a workbook!"; halt }
 
OleAutoArgs args = create() 
put (args, "Before", objSheet) 
oleMethod(objSheet, "Copy", args)

It is a named argument, not a separate one. You specify the location where to copy the worksheet by another worksheet (in the example the worksheet is copied before itself).

Regards, Mathias

Mathias Mamsch, IT-QBase GmbH, Consultant for Requirement Engineering and D00RS

Re: Excel copy paste sheet
SystemAdmin - Fri Jul 08 11:10:05 EDT 2011

Mathias Mamsch - Fri Jul 08 06:12:59 EDT 2011

Sorry I was misreading my old code. Here is a complete Example (start it with an open excel Sheet):

OleAutoObj objEx = oleGetAutoObject("Excel.Application") 
if (null objEx) { print "Please open Excel!"; halt }
 
OleAutoObj objSheet = null 
oleGet(objEx, "ActiveSheet", objSheet)
if (null objEx) { print "Cannot find sheet. Please open a workbook!"; halt }
 
OleAutoArgs args = create() 
put (args, "Before", objSheet) 
oleMethod(objSheet, "Copy", args)

It is a named argument, not a separate one. You specify the location where to copy the worksheet by another worksheet (in the example the worksheet is copied before itself).

Regards, Mathias

Mathias Mamsch, IT-QBase GmbH, Consultant for Requirement Engineering and D00RS

I got the following code to work:

OleAutoObj objEx = oleGetAutoObject("Excel.Application") 
if (null objEx) { print "Please open Excel!"; halt }
 
OleAutoObj objSheet = null 
oleGet(objEx, "ActiveSheet", objSheet)
// changed check for null objEx to objSheet
if (null objSheet) { print "Cannot find sheet. Please open a workbook!"; halt }
 
OleAutoArgs args = create() 
put (args, "Before", objSheet) 
oleMethod(objSheet, "Copy", args)
// deleting OleAutoArgs
delete args

The problem is thus: during execution it throws up a dialog saying "OLE method failed: OLE problem: Select method of Range class failed". I click Confirm and it continues normally. I see that it copy/pasted the sheet. After closing the spreadsheet though, the GUI goes away but I still see the process in Windows Task Manager. So I guess whatever caused the dialog is causing the process to stick around?

Re: Excel copy paste sheet
Mathias Mamsch - Sat Jul 09 04:02:43 EDT 2011

SystemAdmin - Fri Jul 08 11:10:05 EDT 2011

I got the following code to work:

OleAutoObj objEx = oleGetAutoObject("Excel.Application") 
if (null objEx) { print "Please open Excel!"; halt }
 
OleAutoObj objSheet = null 
oleGet(objEx, "ActiveSheet", objSheet)
// changed check for null objEx to objSheet
if (null objSheet) { print "Cannot find sheet. Please open a workbook!"; halt }
 
OleAutoArgs args = create() 
put (args, "Before", objSheet) 
oleMethod(objSheet, "Copy", args)
// deleting OleAutoArgs
delete args

I don't think so. We had some threads about Excel staying open in this forum. The reason are always danling OleAutoObj references that are not cleaned up with oleCloseAutoObject. So you need to explicitly free all OLE handles that you aquire, once you don't need them.

See for example:
https://www.ibm.com/developerworks/forums/thread.jspa?messageID=14638385&#14638385
https://www.ibm.com/developerworks/forums/thread.jspa?messageID=14585977&#14585977

The OLE Method failed message comes from another part of your code (judging from the error message some call to Select Range). You can find it by inserting a dxlHere() into your "Ole Method Failed" error message.

Regards, Mathias

Mathias Mamsch, IT-QBase GmbH, Consultant for Requirement Engineering and D00RS